﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Common;
using System.Data.SqlClient;

public class CtrlBDFunctionalRequirement
{
    private System.Data.SqlClient.SqlDataAdapter adapter;
    private System.Data.SqlClient.SqlCommand command;
    private System.Data.SqlClient.SqlConnection connection;
    private System.Data.DataSet dataSet;
    private String strConnect;
    private String strSql;

    public CtrlBDFunctionalRequirement()
    {
        strConnect = ConfigurationManager.ConnectionStrings["conexion"].ConnectionString;
        connection = new SqlConnection(strConnect);
        connection.Close();
    }

    public void addFunctionalRequirement(FunctionalRequirement functionalRequirement)
    {
        connection.Open();
        strSql = "EXEC addFunctionalRequirement @Name, @Description, @Justification, @Source, @AcceptanceCriteria, @Priority, @State, @ConclusionDate, @StartingDate, @Effort, @IdFather, @Reusable, @IdProject";
        command = new SqlCommand(strSql, connection);

        SqlParameter p1 = new SqlParameter("@Name", functionalRequirement.sName);
        SqlParameter p2 = new SqlParameter("@Description", functionalRequirement.sDescription);
        SqlParameter p3 = new SqlParameter("@Justification", functionalRequirement.sJustification);
        SqlParameter p4 = new SqlParameter("@Source", functionalRequirement.iSource);
        SqlParameter p5 = new SqlParameter("@AcceptanceCriteria", functionalRequirement.sAcceptanceCriteria);
        SqlParameter p6 = new SqlParameter("@Priority", functionalRequirement.iPriority);
        SqlParameter p7 = new SqlParameter("@State", functionalRequirement.iState);
        SqlParameter p8 = new SqlParameter("@ConclusionDate", functionalRequirement.dConclucionDate);
        SqlParameter p9 = new SqlParameter("@StartingDate", functionalRequirement.dStartingDate);
        SqlParameter p10 = new SqlParameter("@Effort", functionalRequirement.fEffort);
        SqlParameter p11 = new SqlParameter("@IdFather", functionalRequirement.iIdFather);
        SqlParameter p12 = new SqlParameter("@Reusable", functionalRequirement.bReusable);
        SqlParameter p13 = new SqlParameter("@IdProject", functionalRequirement.iIdProject);

        command.Parameters.Add(p1);
        command.Parameters.Add(p2);
        command.Parameters.Add(p3);
        command.Parameters.Add(p4);
        command.Parameters.Add(p5);
        command.Parameters.Add(p6);
        command.Parameters.Add(p7);
        command.Parameters.Add(p8);
        command.Parameters.Add(p9);
        command.Parameters.Add(p10);
        command.Parameters.Add(p11);
        command.Parameters.Add(p12);
        command.Parameters.Add(p13);

        command.ExecuteNonQuery();
        connection.Close();
    }

    public void deleteFunctionalRequirement(int iId)
    {
        connection.Open();
        strSql = "EXEC deleteFunctionalRequirement @Id";
        command = new SqlCommand(strSql, connection);

        SqlParameter p1 = new SqlParameter("@Id", iId);

        command.Parameters.Add(p1);

        command.ExecuteNonQuery();
        connection.Close();
    }

    public FunctionalRequirement getFunctionalRequirement(int iId)
    {
        DataTable table = new DataTable();
        connection.Open();

        command = new SqlCommand("EXEC consultFunctionalRequirement @Id", connection);
        SqlParameter p1 = new SqlParameter("@Id", iId);
        command.Parameters.Add(p1);
        adapter = new SqlDataAdapter(command);
        adapter.Fill(table);
        System.IFormatProvider format = new System.Globalization.CultureInfo("es-ES", true);
        FunctionalRequirement functionalRequirement = new FunctionalRequirement(
            Int32.Parse(table.Rows[0][0].ToString()), 
            table.Rows[0][1].ToString(), 
            table.Rows[0][2].ToString(),
            table.Rows[0][3].ToString(),
            Int32.Parse(table.Rows[0][4].ToString()),
            table.Rows[0][5].ToString(),
            Int32.Parse(table.Rows[0][6].ToString()),
            Int32.Parse(table.Rows[0][7].ToString()),
            DateTime.Parse(table.Rows[0][9].ToString()),
            DateTime.Parse(table.Rows[0][8].ToString()),
            float.Parse(table.Rows[0][10].ToString()),
            Int32.Parse(table.Rows[0][11].ToString()),
            (1 == Int32.Parse(table.Rows[0][12].ToString())), 
            Int32.Parse(table.Rows[0][13].ToString()) 
            );

        connection.Close();
        return functionalRequirement;
    }

    public void setFunctionalRequirement(FunctionalRequirement functionalRequirement)
    {
        connection.Open();
        strSql = "EXEC modifyFunctionalRequirement @Id, @Name, @Description, @Justification, @Source, @AcceptanceCriteria, @Priority, @State, @ConclusionDate, @StartingDate, @Effort, @IdFather, @Reusable, @IdProject";
        command = new SqlCommand(strSql, connection);

        SqlParameter p1 = new SqlParameter("@Id", functionalRequirement.iId);
        SqlParameter p2 = new SqlParameter("@Name", functionalRequirement.sName);
        SqlParameter p3 = new SqlParameter("@Description", functionalRequirement.sDescription);
        SqlParameter p4 = new SqlParameter("@Justification", functionalRequirement.sJustification);
        SqlParameter p5 = new SqlParameter("@Source", functionalRequirement.iSource);
        SqlParameter p6 = new SqlParameter("@AcceptanceCriteria", functionalRequirement.sAcceptanceCriteria);
        SqlParameter p7 = new SqlParameter("@Priority", functionalRequirement.iPriority);
        SqlParameter p8 = new SqlParameter("@State", functionalRequirement.iState);
        SqlParameter p9 = new SqlParameter("@ConclusionDate", functionalRequirement.dConclucionDate);
        SqlParameter p10 = new SqlParameter("@StartingDate", functionalRequirement.dStartingDate);
        SqlParameter p11 = new SqlParameter("@Effort", functionalRequirement.fEffort);
        SqlParameter p12 = new SqlParameter("@IdFather", functionalRequirement.iIdFather);
        SqlParameter p13 = new SqlParameter("@Reusable", functionalRequirement.bReusable);
        SqlParameter p14 = new SqlParameter("@IdProject", functionalRequirement.iIdProject);

        command.Parameters.Add(p1);
        command.Parameters.Add(p2);
        command.Parameters.Add(p3);
        command.Parameters.Add(p4);
        command.Parameters.Add(p5);
        command.Parameters.Add(p6);
        command.Parameters.Add(p7);
        command.Parameters.Add(p8);
        command.Parameters.Add(p9);
        command.Parameters.Add(p10);
        command.Parameters.Add(p11);
        command.Parameters.Add(p12);
        command.Parameters.Add(p13);
        command.Parameters.Add(p14);

        command.ExecuteNonQuery();
        connection.Close();
    }

    public DataSet getData()
    {
        dataSet = new DataSet();

        connection.Open();
        strSql = "SELECT Id, Name FROM Functional_Requirements";
        adapter = new SqlDataAdapter(strSql, connection);
        adapter.Fill(dataSet, "Functional Requirements");
        connection.Close();

        return dataSet;
    }

    public DataSet getFilterData(int iIdProject, int iIdFather)
    {
        dataSet = new DataSet();

        connection.Open();
        strSql = "SELECT Id, Name FROM Functional_Requirements WHERE (Id_Project = " + iIdProject + ") AND (Id_father = " + iIdFather + ")";
        adapter = new SqlDataAdapter(strSql, connection);
        adapter.Fill(dataSet, "Filter Functional Requirements");
        connection.Close();

        return dataSet;
    }

    public DataSet getRequirementProjectData(int iIdProject, int iIdRequirement)
    {
        dataSet = new DataSet();

        connection.Open();
        strSql = "SELECT Id, Name FROM Functional_Requirements WHERE (Id_Project = " + iIdProject + ")";
        adapter = new SqlDataAdapter(strSql, connection);
        adapter.Fill(dataSet, "Filter Functional Requirements");
        connection.Close();

        return dataSet;
    }
}
